﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.SqlClient;
using HZ.Data;
namespace HZ.DrugTrade.DAL
{
    public class DAL_HospitalProcureCatalog
    {
        /// <summary>
        /// 增加医疗机构采购目录
        /// </summary>
        /// <param name="hospitalprocurecatalog"></param>
        /// <returns></returns>
        public static int AddHospitalProcureCatalog(HZ.DrugTrade.Model.HospitalProcureCatalog hospitalprocurecatalog)
        {
            IDataParameter[] paras = HZ.Data.DAL_Helper.GetDataParameter<HZ.DrugTrade.Model.HospitalProcureCatalog>(hospitalprocurecatalog);

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_HospitalProcureCatalog_Add", paras);
            return (int)paras[paras.Length - 1].Value;
        }

        /// <summary>
        /// 医疗机构增加采购目录(批量)
        /// </summary>
        /// <param name="hospitalprocurecatalogList">医疗机构采购目录(医疗机构编号，采购目录编号必填)</param>
        public static int AddHospitalProcureCatalog_Batch(IList<HZ.DrugTrade.Model.HospitalProcureCatalog> hospitalprocurecatalogList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_ProcureCatalogIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(hospitalprocurecatalogList,"ProcureCatalogID"  ,strSplit)),
            //    new SqlParameter("@in_HospitalIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(hospitalprocurecatalogList,"HospitalID"  ,strSplit)),
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_HospitalProcureCatalog_Batch_Add", paras);
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                foreach (var item in hospitalprocurecatalogList)
                {
                    if (int.Parse(h.ExecuteScalar(string.Format(@" SELECT count(*)   FROM  TB_HospitalProcureCatalog  WHERE  HospitalID = '{0}' AND ProcureCatalogID = '{1}'", item.HospitalID, item.ProcureCatalogID)).ToString()) == 0)
                    {
                        double ProcurePrice = 0;
                        string strProcurePrice = h.ExecuteScalar(string.Format(" SELECT ProcurePrice FROM  TB_ProcureCatalog WHERE  ProcureCatalogID='{0}'", item.ProcureCatalogID)).ToString();
                        if (!string.IsNullOrEmpty(strProcurePrice))
                        {
                            ProcurePrice = double.Parse(strProcurePrice);
                        }
                        h.ExecuteNonQuery(string.Format(@"INSERT INTO TB_HospitalProcureCatalog (  HospitalID,ProcureCatalogID,IsUsing,SellPrice, Stock)
		                VALUES('{0}','{1}',1,fun_GetSellPrice({2}), 0)", item.HospitalID, item.ProcureCatalogID, ProcurePrice));

                    }
                }
                h.CommitTrans();
                Flag = 1;
            }
            catch
            {
                h.RollTrans();
                Flag = 0;
            }
            return Flag;
        }


        /// <summary>
        /// 更新医疗机构采购目录
        /// </summary>
        /// <param name="hospitalprocurecatalog"></param>
        /// <returns></returns>
        public static int UpdateHospitalProcureCatalog(HZ.DrugTrade.Model.HospitalProcureCatalog hospitalprocurecatalog)
        {
            IDataParameter[] paras = HZ.Data.DAL_Helper.GetDataParameter<HZ.DrugTrade.Model.HospitalProcureCatalog>(hospitalprocurecatalog);
            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_HospitalProcureCatalog_Update", paras);
            return (int)paras[paras.Length - 1].Value;
        }


        /// <summary>
        /// 撤废/恢复医疗机构采购目录
        /// </summary>
        /// <param name="hospitalprocurecatalogList">医疗机构自身采购目录信息【ProcureCatalogID，HospitalID不为空】</param>
        /// <param name="flag">是否启用（0否;1是）</param>
        /// <returns></returns>
        public static int ValidateHospitalProcureCatalog_Batch(IList<HZ.DrugTrade.Model.HospitalProcureCatalog> hospitalprocurecatalogList, int flag)
        {
            string strSplit = ",";
            IDataParameter[] paras = new IDataParameter[] { 
                new SqlParameter("@in_ProcureCatalogIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(hospitalprocurecatalogList,"ProcureCatalogID"  ,strSplit)),
                new SqlParameter("@in_HospitalIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(hospitalprocurecatalogList,"HospitalID"  ,strSplit)),
                new SqlParameter("@in_IsUsing", flag),
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_HospitalProcureCatalog_Batch_Update", paras);

            //return (int)paras[paras.Length - 1].Value;

                //UPDATE [dbo].[TB_HospitalProcureCatalog]
                //SET    [IsUsing] = @in_IsUsing
                //WHERE  [ProcureCatalogID] = @in_ProcureCatalogID
                //       AND [HospitalID] = @in_HospitalID

            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                string sqlConfirm = @" UPDATE  TB_HospitalProcureCatalog 
                                                        SET     IsUsing  = @in_IsUsing
                                                        WHERE   ProcureCatalogID  = @in_ProcureCatalogID AND HospitalID=@in_HospitalID";

                foreach (var item in hospitalprocurecatalogList)
                {
                    List<string> Confirmparasnames = new List<string>();
                    List<object> Confirmvalues = new List<object>();

                    Confirmparasnames.Add("@in_HospitalID");
                    Confirmvalues.Add(string.IsNullOrEmpty(item.HospitalID) ? "" : item.HospitalID.Trim());

                    Confirmparasnames.Add("@in_ProcureCatalogID");
                    Confirmvalues.Add(string.IsNullOrEmpty(item.ProcureCatalogID) ? "" : item.ProcureCatalogID.Trim());

                    Confirmparasnames.Add("@in_IsUsing");
                    Confirmvalues.Add(flag);

                    
                    h.ExecuteNonQuery(CommandType.Text, sqlConfirm, HZ.Data.DBParameterFactory.GetIDbDataParameterList(Confirmparasnames, Confirmvalues).ToArray());

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug(typeof(DAL_User).FullName, "企业确认采购单明细不成功", ex);
                retValue = 0;
            }

            return retValue;
        }

        /// <summary>
        /// 获取医疗机构采购目录
        /// </summary>
        /// <param name="query">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.HospitalProcureCatalog> GetHospitalProcureCatalog(HZ.DrugTrade.Model.Query.HospitalProcureCatalog query, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.HospitalProcureCatalog>(query, false);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_HospitalProcureCatalog WHERE 1=1  {0} order by procurecatalogid,hospitalid", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalProcureCatalog>(dt);
        }

        /// <summary>
        /// 条件查询【地区医疗机构采购目录】
        /// </summary>
        /// <param name="where">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.AreaHospitalProcureCatalo> GetAreaHospitalProcureCatalogByWhere(HZ.DrugTrade.Model.Query.AreaHospitalProcureCatalo query, int currentpage, int pagesize, out int totalrecord)
        {
            //  string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.AreaHospitalProcureCatalo>(query);
            string where = "";

            //采购目录编号
            string ProcureCatalogID = query.ProcureCatalogID;

            //地区编号
            string AreaID = "";
            if (!string.IsNullOrEmpty(query.AreaID))
            {
                //省：32江苏省；市：3201南京市；县区：320103白下区
                AreaID = HZ.Common.MyString.TrimEnd(query.AreaID, "00");
            }

            if (ProcureCatalogID.Trim() != "")
            {
                where = string.Format(" AND ProcureCatalogID =  '{0}' ", ProcureCatalogID);
            }

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_AreaHospitalProcureCatalog  WHERE 1=1  AND AreaID LIKE  '{0}%'  {1} ORDER BY AreaID ", AreaID, where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.AreaHospitalProcureCatalo>(dt);
        }

        /// <summary>
        /// 条件查询【地区医疗机构】[可根据HospitalName，AreaID(省级-省级编号,市级-市级编号)查询]
        /// </summary>
        /// <param name="where">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.AreaHospitalProcureCatalo> GetAreaHospitalByWhere(HZ.DrugTrade.Model.Query.AreaHospitalProcureCatalo query, int currentpage, int pagesize, out int totalrecord)
        {

            string where = "";
            //医疗机构名称
            string HospitalName = query.HospitalName;

            //地区编号
            string AreaID = "";
            if (!string.IsNullOrEmpty(query.AreaID))
            {
                //省：32江苏省；市：3201南京市；县区：320103白下区
                AreaID = HZ.Common.MyString.TrimEnd(query.AreaID, "00");
            }


            if (HospitalName.Trim() != "")
            {
                where = string.Format(" AND HospitalName like  '%{0}%' ", HospitalName);
            }
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT DISTINCT
                                    AreaName,
                                    FatherAreaID,
                                    HospitalName,
                                    HospitalAddress,
                                    ContactPerson,
                                    ContactTel,
                                    HospitalID,
                                    AreaID ,
                                    '' as ProcureCatalogID,
                                    1 as IsUsing FROM View_AreaHospitalProcureCatalog 
                                    WHERE 1=1  AND AreaID LIKE  '{0}%'  {1}  ORDER BY AreaID ", AreaID, where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.AreaHospitalProcureCatalo>(dt);
        }


        /// <summary>
        /// 条件查询[可根据查询]
        /// </summary>
        /// <param name="where">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.HospitalProcureCatalog> GetAreaProductByWhere(HZ.DrugTrade.Model.Query.HospitalProcureCatalog query, int currentpage, int pagesize, out int totalrecord)
        {
            string where = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition<HZ.DrugTrade.Model.Query.HospitalProcureCatalog>(query, true);
            //DISTINCT ProcureCatalogID,ProductName,MedicineModelName,SortName,GoodsName,Factor,Outlookc,Unit,Pack,CompanyName_SC ,SortSource,GoodsID,Material,PackUnit,ProcurePrice,ProjectName,AddTime,IsUsing
            string sql = "SELECT * ";
            sql += "FROM View_AreaProductList  WHERE 1=1 {0}";

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(sql, where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.HospitalProcureCatalog>(dt);
        }
        /// <summary>
        /// 检测医疗机构和配送企业是否发生配送关系 发生配送交易禁止删除
        /// </summary>
        /// <param name="HospitalID"></param>
        /// <param name="CompanyID_PS"></param>
        /// <returns></returns>
        public static bool GetBooleanHospitalPurchaseOrder(HZ.DrugTrade.Model.Query.HospitalProcureCatalog where)
        {
            try
            {
                string sql = "";
//                sql = @" If exists(select top 1  companyid_ps From View_purchaseOrderDetail 
//                                            Where ProcureCataLogID=@in_ProcureCataLogID
//                                            And HospitalID=@in_HospitalID and CompanyID_PS=@in_CompanyID_PS)
//                              Select 1
//                            Else  
//                               select 0 ";
//                IDataParameter[] paras = new IDataParameter[] { 
//                 new SqlParameter("@in_ProcureCataLogID",where.ProcureCatalogID),
//                new SqlParameter("@in_HospitalID", where.HospitalID),
//                new SqlParameter("@in_CompanyID_PS", where.Companyid_Ps)
               
//            };
                sql=string.Format(@"select count(companyid_ps) From View_purchaseOrderDetail 
                                            Where ProcureCataLogID='{0}'
                                            And HospitalID='{1}' and CompanyID_PS='{2}'",where.ProcureCatalogID,where.HospitalID,where.Companyid_Ps);
                int returnVal = int.Parse(HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql).ToString());
                if (returnVal > 0)
                {
                    return true;
                }
                else
                { return false; }
            }
            catch (Exception ex)
            {

                return true;
            }
        }
        /// <summary>
        /// 查看医疗机构实际价格历史记录
        /// add by Meng 2013-04-22
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetHospitalPriceLog(string where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"select * from (select a.*,b.goodsname,b.Outlookc,b.Material,b.PackUnit,b.CompanyName_SC,b.CompanyName_TB,b.ProcurePrice from 
tb_hospitalpricelog a left join view_procurecatalog_tb b on a.procurecatalogid = b.procurecatalogid) where 1=1 {0}",where);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
    }
}
